package demo.dao;


import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import demo.entity.Reader;
//import demo.entity.Course;
import demo.util.DBUtil;
import demo.util.MD5Util;


public class ReaderDao {
	public Reader findById(String id) {
		Connection con = DBUtil.getConnection();
		String selectReaderSqlStr = "SELECT * FROM reader where id='" + id + "'";
		if (con != null) {
			try {
				ResultSet rs = con.createStatement().executeQuery(selectReaderSqlStr);
				List<Reader> readerList = rsToReader(rs);
				if (readerList != null && readerList.size() != 0)
					return readerList.get(0);
			} catch (SQLException e) {
				processSqlError(e);
			} finally {
				DBUtil.closeConnection(con);
			}
		}
		return null;
	}
	
	public List<Reader> findByName(String name) {
		Connection con = DBUtil.getConnection();
		String selectReaderByNameSqlStr = "SELECT * FROM reader where name='" + name + "'";
		if (con != null) {
			try {
				ResultSet rs = con.createStatement().executeQuery(selectReaderByNameSqlStr);
				List<Reader> readerList = rsToReader(rs);
				//if (readerList != null && readerList.size() != 0)
					return readerList;
			} catch (SQLException e) {
				processSqlError(e);
			} finally {
				DBUtil.closeConnection(con);
			}
		}
		return null;
	}
	
	private List<Reader> rsToReader(ResultSet rs) {
		List<Reader> readerList = new ArrayList<>();
		try {
			while (rs.next()) {
				String id = rs.getString(1);
				String password = rs.getString(2);
				String name=rs.getString(3);
				String sex=rs.getString(4);
				Date birth=rs.getDate(5);
				String pro=rs.getString(6);
				String grade=rs.getString(7);
				String tel=rs.getString(8);
				String email=rs.getString(9);
				Reader reader = new Reader(id,password,name,sex,birth,pro,grade,tel,email);
				readerList.add(reader);
			}
			return readerList;
		} catch (SQLException e) {
			processSqlError(e);
		}
		return null;
	}
	
	private void processSqlError(Exception e) {
		e.printStackTrace();
	}
	
	public int save(Reader reader) {
		Connection con = DBUtil.getConnection();
		String insertReaderSqlStr = "INSERT INTO reader(id, password,name,sex,birth,pro,grade,tel,email) " + "VALUES(?, ?, ?,?,?, ?, ?,?,?)";
		if (con != null) {
			try {
				// 如果用户已存在，返回0
				if (findById(reader.getId()) != null)
					return 0;
				PreparedStatement preparedStatement = con.prepareStatement(insertReaderSqlStr);
				preparedStatement.setString(1, reader.getId());
				preparedStatement.setString(2, reader.getPassword());
				preparedStatement.setString(3, reader.getName());
				preparedStatement.setString(4, reader.getSex());
				preparedStatement.setDate(5, reader.getBirth());
				preparedStatement.setString(6, reader.getPro());
				preparedStatement.setString(7, reader.getGrade());
				preparedStatement.setString(8, reader.getTel());
				preparedStatement.setString(9, reader.getEmail());

				return preparedStatement.executeUpdate();
			} catch (SQLException e) {
				processSqlError(e);
			} finally {
				DBUtil.closeConnection(con);
			}
		}
		return -1;
	}
	
	public List<Reader> findAll() {
		Connection con = DBUtil.getConnection();
		String selectAllSqlStr = "SELECT * FROM reader";
		if (con != null) {
			try {
				ResultSet rs = con.createStatement().executeQuery(selectAllSqlStr);
				List<Reader> readerList = rsToReader(rs);
				return readerList;
			} catch (SQLException e) {
				processSqlError(e);
			} finally {
				DBUtil.closeConnection(con);
			}
		}
		return null;
	}
	
	public int update(Reader reader) {
		Connection con = DBUtil.getConnection();
		String updateReaderSqlStr = "UPDATE reader SET password=?, name=?, sex=?, birth=?, pro=?, grade=?, tel=?, email=? WHERE id=?";
		if (con != null) {
			if (findById(reader.getId()) != null) {
				try {
					PreparedStatement preparedStatement = con.prepareStatement(updateReaderSqlStr);
					preparedStatement.setString(1, reader.getPassword());
					preparedStatement.setString(2, reader.getName());
					preparedStatement.setString(3, reader.getSex());
					preparedStatement.setDate(4, reader.getBirth());
					preparedStatement.setString(5, reader.getPro());
					preparedStatement.setString(6, reader.getGrade());
					preparedStatement.setString(7, reader.getTel());
					preparedStatement.setString(8, reader.getEmail());
					preparedStatement.setString(9, reader.getId());
					return preparedStatement.executeUpdate();
				} catch (SQLException e) {
					processSqlError(e);
				} finally {
					DBUtil.closeConnection(con);
				}
			} else
				return 0;
		}
		return -1;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
}
